﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_ListLatestPhotoalbums')
BEGIN
    PRINT 'Dropping Procedure proc_cm_ListLatestPhotoalbums'
    DROP  Procedure  proc_cm_ListLatestPhotoalbums
END
GO

PRINT 'Creating Procedure proc_cm_ListLatestPhotoalbums'
GO

CREATE PROCEDURE [dbo].[proc_cm_ListLatestPhotoalbums]
	@pPageSize int = 0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

	select top(@pPageSize)
		pa.[photoalbum_id]
		,pa.[html_page_id] 
		,pa.[title_txt]
		,pa.[desc_txt]
		,pa.[create_dttm]
		,pa.[update_dttm]
		,pa.[latest_photo_id]
		,p2.[filepath_txt]
		,pa.[archive_ind]
		,hp.[page_url]
		,count(p.filename_txt) as photo_cnt
	from
		[dbo].[tbl_photoalbum] pa (nolock)
		inner join [dbo].[tbl_photo] p (nolock) on p.photoalbum_id = pa.photoalbum_id
		inner join [dbo].[tbl_photo] p2 (nolock) on pa.latest_photo_id = p2.photo_id
		inner join [dbo].[tbl_v2_HtmlPage] hp (nolock) on pa.html_page_id = hp.html_page_id
	where
		pa.[archive_ind] = 0
	group by
		pa.[photoalbum_id]
		,pa.[html_page_id] 
		,pa.[title_txt]
		,pa.[desc_txt]
		,pa.[create_dttm]
		,pa.[update_dttm]
		,pa.[latest_photo_id]
		,p2.[filepath_txt]
		,pa.[archive_ind]
		,hp.[page_url]
	order by pa.[create_dttm] desc
END
GO

GRANT EXEC ON dbo.proc_cm_ListLatestPhotoalbums TO PUBLIC
GO

